package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import beans.MessageBean;
import beans.UserBean;
import beans.VenueBean;

public class VenueDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;

	public static VenueBean createVenue(VenueBean newVenue) {
		Statement stmt = null;    

		String insertQuery = "insert into venue (name, address, city, region, country) values ('" +
				newVenue.getName() + "', '" +
				newVenue.getAddress() + "', '" +
				newVenue.getCity() + "', '" +
				newVenue.getRegion() + "', '" +
				newVenue.getCountry() + "')";
		String getIdQuery = "select id from venue order by id desc";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(insertQuery);	        
			rs = stmt.executeQuery(getIdQuery);
			rs.next();
			int id = rs.getInt("id");
			newVenue.setId(id);
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);

		} 
		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return newVenue;
	}

	public static ArrayList<VenueBean> recentVenues(UserBean host) {
		Statement stmt = null;    

		String username = host.getEmail();

		String searchQuery = "select distinct id,venue.name,address,city,region,country from venue join event on venue=id where host='" + username +"'";

		ArrayList<VenueBean> venueList = new ArrayList<VenueBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				VenueBean venue = new VenueBean(rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
				venue.setId(rs.getInt(1));
				venueList.add(venue);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return venueList;
	}

	public static VenueBean getVenue(VenueBean venue) {
		Statement stmt = null;    

		int venueId = venue.getId();

		String searchQuery = "select * from venue where id='" + venueId +"'";
		VenueBean result = new VenueBean();
		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				result.setName(rs.getString(2));
				result.setAddress(rs.getString(3));
				result.setCity(rs.getString(4));
				result.setRegion(rs.getString(5));
				result.setCountry(rs.getString(6));
				result.setId(venueId);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return result;
	}
}